package com.wframe.project.jnd.dao;

import java.math.BigDecimal;
import java.util.*;

import com.wframe.project.jnd.entity.Picture;
import com.wframe.project.jnd.vo.Product;
import com.wframe.project.jnd.vo.UrgentProduct;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;
import com.alibaba.fastjson.JSON;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.wframe.framework.database.BaseDao;
import com.wframe.framework.database.ParmSqlVo;
import com.wframe.framework.util.ConversionUtil;
import com.wframe.project.former.entity.ProductsAttrvalBean;

@Repository
public class ProductDao extends BaseDao {

    public PageResult queryWebProduct(JdataBean parms, int size, int pageno) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.unitname,p.code productcode,p.name productname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status=2";
        List patmlist = new ArrayList();
        if (parms != null) {
            Object[] keys = parms.getDataMap().keySet().toArray();
            for (Object key : keys) {
                if (StringUtils.isNotBlank(parms.getString(key.toString()))) {
                    if ("classcode".equals(key.toString()) && !"1".equals(parms.getString(key.toString()))) {
                        sql += " and t.productid in (select productid from wb_products_and_class where (classcode like ? or classcode=?))";
                        patmlist.add(parms.getString(key.toString()) + ".%");
                        patmlist.add(parms.getString(key.toString()));
                    } else if ("attritems".equals(key.toString()) && StringUtils.isNotBlank(parms.getString(key.toString()))) {
                        ParmSqlVo vo = ConversionUtil.arrayToInSql(parms.getString(key.toString()).split(","));
                        sql += " and t.productid in (select productid from wb_products_attrval where attrid in (" + vo.getInSql() + "))";
                        for (Object obj : vo.getParms())
                            patmlist.add(obj);
                    } else if ("instock".equals(key.toString()) && "1".equals(parms.getString(key.toString()))) {
                        sql += " and t.amount>0";
                    } else if ("querylike".equals(key.toString())) {
                        sql += " and (p.name like ? or p.acronymname like ? or t.name like ? or p.code like ? or t.productid in (select b.productid from wb_products_attr a,wb_products_attrval b where a.id=b.attrid and ((a.code='B_tymz' and b.val like ?) or (a.code='B_chanjia' and b.val like ?))))";
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                    } else if ("floorid".equals(key.toString())) {
                        //楼层筛选
                        sql += " and t.id in (select priceid from wb_floor_item_products i,wb_floor_item f where i.itemid=f.id and f.floorid=?)";
                        patmlist.add(parms.getString(key.toString()));
                    } else if ("flooritemid".equals(key.toString())) {
                        //楼层类目筛选
                        sql += " and t.id in (select priceid from wb_floor_item_products i where itemid=?)";
                        patmlist.add(parms.getString(key.toString()));
                    }
                }
            }
        }
        String order = "";
        if (parms != null) {
            if ("1".equals(parms.getString("ordertype")))
                order = " t.popularity " + parms.get("orderby", "[asc,desc]");
            else if ("2".equals(parms.getString("ordertype")))
                order = " t.salesnum " + parms.get("orderby", "[asc,desc]");
            else if ("3".equals(parms.getString("ordertype")))
                order = " t.price " + parms.get("orderby", "[asc,desc]");

        }
        if (StringUtils.isNotBlank(order))
            sql += " order by " + order;
//        else
//            sql += " order by p.orderno,p.id";
        return querySql2(sql, patmlist, size, pageno);
    }

    public PageResult queryFloorProduct(String floorid, String itemid, int size, int pageno) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.unitname,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status=2";
        List patmlist = new ArrayList();
        if (StringUtils.isNotBlank(floorid)) {
            sql += " and t.id in (select priceid from wb_floor_item_products where itemid in (select id from wb_floor_item where floorid=?))";
            patmlist.add(floorid);
        }
        if (StringUtils.isNotBlank(itemid)) {
            sql += " and t.id in (select priceid from wb_floor_item_products where itemid =?)";
            patmlist.add(itemid);
        }
        sql += " order by p.orderno,p.id";
        PageResult pr = querySql2(sql, patmlist, size, pageno);
        List<JdataBean> productlist = pr.getList();
        for (JdataBean product : productlist) {
            String photo = product.getString("photoview");
            if (StringUtils.isBlank(photo)) photo = product.getString("product_photoview");
            product.put("photo", photo);
            String attrjson = product.getString("attrvals");
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.getString("productid"), ew);
                }
            }
        }
        return pr;
    }

    public List<Map> getProductPrices(String productid) throws Exception {
        return querySqlMap("select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,t.photo,t.photodataid,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount from wb_products_prices t where productid=? order by id", new Object[]{productid});
    }

    public PageResult queryChannelProduct(String code, int size, int pageno) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status<>-1";
        List patmlist = new ArrayList();
        if (StringUtils.isNotBlank(code)) {
            sql += " and t.id in (select priceid from wb_channel_product where channelcode=?)";
            patmlist.add(code);
        }
        sql += " order by p.orderno,p.id";
        PageResult pr = querySqlMap2(sql, patmlist, size, pageno);
        List<Map> productlist = pr.getList();
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return pr;
    }

    public PageResult ChannelProductSort(String code, int size, int pageno) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status=2 ";
        List patmlist = new ArrayList();
        if (StringUtils.isNotBlank(code)) {
            sql += " and t.id in (select priceid from wb_channel_product where channelcode=? order by p.orderno desc)";
            patmlist.add(code);
        }
        PageResult pr = querySqlMap2(sql, patmlist, size, pageno);
        List<Map> productlist = pr.getList();
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return pr;

    }

    /**
     * by ljh  用于微信专题栏目
     */
    public PageResult queryChannelProduct1(String code, int size, int pageno) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status<>-1";
        List patmlist = new ArrayList();
        if (StringUtils.isNotBlank(code)) {
            sql += " and t.id in (select priceid from wb_channel_product where channelcode=?)";
            patmlist.add(code);
        }
        sql += " order by t.salesnum desc,p.orderno,p.id";
        PageResult pr = querySqlMap2(sql, patmlist, size, pageno);
        List<Map> productlist = pr.getList();
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return pr;
    }

    public JdataBean getProductPrice(String priceid) {
        return getObject("select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,t.photo,t.photodataid,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount from wb_products_prices t where id=?", new Object[]{priceid});
    }

    public JdataBean getProductid(String productid) {
        return getObject("select p.id,pp.price,p.name,ap.filepath,c.name category from wb_products AS p INNER JOIN wb_products_prices AS pp ON p.id = pp.productid left JOIN app_pictures AS ap ON p.id = ap.id left join wb_products_and_class pa on p.id=pa.productid left JOIN wb_products_class c ON pa.classid=c.id WHERE p.id = ? ", new Object[]{productid});
    }

    public JdataBean getProduct(String id) throws Exception {
        JdataBean product = getObject("select * from wb_products where id=?", new Object[]{id});
        product.put("unit", product.getByDict("unit", "ProductUnit"));
        return product;
    }

    public List<JdataBean> getProductAttrs(String productid) {
        List<JdataBean> list = querySql("select v.*,a.code,a.name,a.type from wb_products_attrval v,wb_products_attr a where v.attrid=a.id and v.productid=?", new Object[]{productid});
        for (JdataBean attr : list) {
            String valtxt = attr.getString("val");
            if (attr.getInt("type") == 2) {
                JdataBean item = getObject("select text from wb_products_attritem where attrid=? and code=?", new Object[]{attr.getString("attrid"), attr.getString("val")});
                if (item != null) valtxt = item.getString("text");
            }
            attr.put("valtxt", valtxt);
        }
        return list;
    }

    public String getProductClsid(String productid) throws Exception {
        JdataBean product = getObject("select classid from wb_products_and_class where productid=?", new Object[]{productid});
        return product.getString("classid");
    }

    public String getProductClsCode(String productid) {
        JdataBean product = getObject("select classcode from wb_products_and_class where productid=?", new Object[]{productid});
        if (product != null) return product.getString("classcode");
        else return "";
    }

    public List<Map> querySubProductCls(String pid) {
        return querySqlMap("select * from wb_products_class where pid=? order by orderno,id", new Object[]{pid});
    }

    public List<Map> querySubProductClsByCode(String clscode) {
        return querySqlMap("select * from wb_products_class where pid in (select id from wb_products_class where code = ?) order by orderno,id", new Object[]{clscode});
    }

    public List<Map> queryProductClsByCode(String codestr) {
        if (StringUtils.isNotBlank(codestr))
            return querySqlMap("select name,code from wb_products_class where code in (" + codestr + ") order by code", null);
        return new ArrayList<Map>();
    }

    /**
     * 查询商品属性
     *
     * @param clscode
     * @return
     */
    public List<Map> queryProductAttr(String clscode) {
        String sql = "select * from wb_products_attr where (classcode like ? or classcode=?) and status<>-1 and tag=2 order by orderno,id";
        return querySqlMap(sql, new Object[]{clscode + ".%", clscode});
    }

    public List<Map> queryProductAttrItemAll(String clscode) {
        return querySqlMap("select * from wb_products_attritem where attrid in (select id from wb_products_attr where classcode like ? or classcode=?) and status<>-1 order by attrid,orderno,id", new Object[]{clscode + ".%", clscode});
    }

    public List<Map> queryClsNav(String[] clscodes) {
        ParmSqlVo sql = ConversionUtil.arrayToInSql(clscodes);
        return querySqlMap2("select * from wb_products_class where code in (" + sql.getInSql() + ") order by code", sql.getParms());
    }

    public List<Map> queryAttrNav(String[] itemids) {
        ParmSqlVo sql = ConversionUtil.arrayToInSql(itemids);
        return querySqlMap2("select i.id,i.code,i.text,i.attrid,a.name attrname from wb_products_attritem i,wb_products_attr a where i.attrid=a.id and i.id in (" + sql.getInSql() + ") order by a.orderno,a.id", sql.getParms());
    }

    public void queryControlsales(JdataBean user, JdataBean com, List products) {
        List<String> productids = new ArrayList<String>();
        for (int i = 0; i < products.size(); i++) {
            Object obj = products.get(i);
            String id = "";
            if (obj instanceof JdataBean) id = ((JdataBean) obj).getString("productid");
            else id = ((Map) obj).get("productid").toString();
            productids.add(id);
        }
        if (productids.size() > 0) {
            if (com != null) {
                user.put("province", com.getString("province"));
                user.put("city", com.getString("city"));
                user.put("region", com.getString("region"));
            } else user = new JdataBean();
            List<JdataBean> controls = queryControlsales2(user.getString("id"), user.getString("UserGroup"), user.getString("pricegrade"), user.getString("province"), user.getString("city"), user.getString("region"), productids.toArray(new String[]{}));
            for (JdataBean control : controls) {
                for (int i = 0; i < products.size(); i++) {
                    Object obj = products.get(i);
                    String productid = "";
                    if (obj instanceof JdataBean) {
                        productid = ((JdataBean) obj).getString("productid");
                        if (productid.equals(control.getString("productid"))) {
                            ((JdataBean) obj).put("canbuy", 1);//可以购买
                            break;
                        }
                    } else {
                        productid = ((Map) obj).get("productid").toString();
                        if (productid.equals(control.getString("productid"))) {
                            ((Map) obj).put("canbuy", 1);//可以购买
                            break;
                        }
                    }
                }
            }
        }
    }

    public List<JdataBean> queryControlsales(String userid, String UserGroup, String pricegrade, String province, String city, String area, String[] productids) {
        ParmSqlVo sqlvo = ConversionUtil.arrayToInSql(productids);
        String citysql = "select id from wb_area_trad where (province like ? and IFNULL(city,'') ='' and IFNULL(area,'') ='') or (province like ? and city like ? and IFNULL(area,'') ='') or (province like ? and city like ? and area like ?)";
        String sql = "select id as productid from wb_products where (id in ("
                + "select productid from wb_products_controlsales where productid in (" + sqlvo.getInSql() + ") and ("
                + "(type=4 and val = ?) or (type=1 and val = ?) or (type=2 and val= ?) or (type=3 and val in (" + citysql + "))"
                + ")) or iscontrol=0)";
        List parms = new ArrayList();
        sqlvo.getParms().add(userid);
        sqlvo.getParms().add(UserGroup);
        sqlvo.getParms().add(pricegrade);
        if (StringUtils.isBlank(province)) province = "null";
        if (StringUtils.isBlank(city)) city = "null";
        if (StringUtils.isBlank(area)) area = "null";
        sqlvo.getParms().add(province + "%");
        sqlvo.getParms().add(province + "%");
        sqlvo.getParms().add(city + "%");
        sqlvo.getParms().add(province + "%");
        sqlvo.getParms().add(city + "%");
        sqlvo.getParms().add(area + "%");
        return querySql2(sql, sqlvo.getParms());
    }

    /**
     * 查询一组商品对用户是否控销,对queryControlsales()的优化
     *
     * @param userid
     * @param UserGroup
     * @param pricegrade
     * @param province
     * @param city
     * @param area
     * @param productids
     * @return
     */
    public List<JdataBean> queryControlsales2(String userid, String UserGroup, String pricegrade, String province, String city, String area, String[] productids) {
        ParmSqlVo sqlvo = ConversionUtil.arrayToInSql(productids);
        String citysql = "select id from wb_area_trad where (province like ? and IFNULL(city,'') ='' and IFNULL(area,'') ='') or (province like ? and city like ? and IFNULL(area,'') ='') or (province like ? and city like ? and area like ?)";
        String sql = "select id as productid from wb_products where (id in ("
                + "select productid from wb_products_controlsales where productid in (" + sqlvo.getInSql() + ") and ("
                + "(type=4 and val = ?) or (type=1 and val = ?) or (type=2 and val= ?) or (type=3 and val in (" + citysql + "))"
                + ")) or (id in (" + sqlvo.getInSql() + ") and iscontrol=0))";
        List parms = sqlvo.getParms();
        List inParms = new ArrayList();
        inParms.addAll(parms);
        parms.add(userid);
        parms.add(UserGroup);
        parms.add(pricegrade);
        if (StringUtils.isBlank(province)) province = "null";
        if (StringUtils.isBlank(city)) city = "null";
        if (StringUtils.isBlank(area)) area = "null";
        parms.add(province + "%");
        parms.add(province + "%");
        parms.add(city + "%");
        parms.add(province + "%");
        parms.add(city + "%");
        parms.add(area + "%");
        parms.addAll(inParms);
        return querySql2(sql, sqlvo.getParms());
    }

    public List<JdataBean> queryPricegrade(String pricegrade, String[] productids) {
        ParmSqlVo sqlvo = ConversionUtil.arrayToInSql(productids);
        sqlvo.getParms().add(pricegrade);
        return querySql2("select * from wb_products_pricegrade where productid in (" + sqlvo.getInSql() + ") and grade=?", sqlvo.getParms());
    }

    public List<JdataBean> loadHomeProductclass(String base) {
        return querySql("select * from wb_products_class where code like ? and status<>-1  order by orderno is null asc,orderno,id", new Object[]{base + ".%"});
    }

    /**
     * 加载二级类目商品
     *
     * @return
     */
    public List<JdataBean> loadFeaturedProduct() {
        String sql = "select t.*,f.classcode,p.unitname,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_class_featured f"
                + " inner join wb_products_prices t on t.id=f.priceid"
                + " inner join wb_products p on p.id=f.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status<>-1 order by p.orderno,p.id";
        return querySql(sql, null);
    }

    public void addpopularity(String priceid) {
        execute("update wb_products_prices set popularity=popularity+1 where id=?", new Object[]{priceid});
    }

    public JdataBean getFavorite(String userid, String priceid) {
        return getObject("select id from wb_favorite where userid=? and dataid=? and type=1", new Object[]{userid, priceid});
    }

    public void addFavorite(String userid, String priceid, String productid) throws Exception {
        JdataBean get = getFavorite(userid, priceid);
        if (get == null) {
            Map data = new HashMap();
            data.put("type", 1);
            data.put("dataid", priceid);
            data.put("productid", productid);
            data.put("userid", userid);
            data.put("createtime", new Date().getTime() / 1000);
            save("wb_favorite", data);
        }
    }

    public void delFavorite(String userid, String priceid) {
        execute("delete from wb_favorite where userid=? and dataid=? and type=1", new Object[]{userid, priceid});
    }

    public PageResult queryFavorite(String userid, int size, int pageno) {
        String sql = "select c.id oftenid,t.id,t.amount,t.djamount,CASE  WHEN pp.price>0 THEN pp.price ELSE t.price END as price,p.code productcode,p.name productname,p.unitname,p.attrvals,p.status,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_favorite c"
                + " inner join wb_products_prices t on t.id=c.dataid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " join wbuser u on c.userid=u.id"
                + " left join wb_products_pricegrade pp on c.productid=pp.productid and u.pricegrade=pp.grade"
                + " where p.status<>-1 and c.userid=? and c.type=1 order by c.id";
        PageResult pr = querySqlMap(sql, new Object[]{userid}, size, pageno);
        List<Map> productlist = pr.getList();
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo)) {
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            }
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return pr;
    }

    public PageResult queryHotProducts(int size, int pageno) throws Exception {
        String sql = "select c.productid,c.orderno,c.priceid,c.status,t.name attrname,t.id attrid,t.price,t.price_s,p.code productcode,p.name productname,p.unitname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_hot_products c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where  p.status=2 and c.status=2";
        List parmlist = new ArrayList();
        sql += " order by c.orderno desc,c.id desc";
        PageResult pr = querySql2(sql, null, size, pageno);
        List<JdataBean> productlist = pr.getList();
        for (int i = 0; i < productlist.size(); i++) {
            JdataBean product = productlist.get(i);
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode().toLowerCase(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return pr;
    }

    public JdataBean getSale(String priceid) {
        return getObject("SELECT p.`status` from wb_products p join wb_products_prices t on p.id=t.productid WHERE t.id=?", new Object[]{priceid});
    }

    public PageResult findFavorites(String userid, int pageSize, int pageNo) {
        String sql = "select c.id oftenid,t.id,(IFNULL(t.amount,0)-IFNULL(t.djamount,0)) amount,CASE  WHEN pp.price>0 THEN pp.price ELSE t.price END as price,p.code productcode,p.name productname,p.unitname,p.attrvals,p.status,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_favorite c"
                + " inner join wb_products_prices t on t.id=c.dataid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " join wbuser u on c.userid=u.id"
                + " left join wb_products_pricegrade pp on c.productid=pp.productid and u.pricegrade=pp.grade"
                + " where p.status<>-1 and c.userid=? and c.type=1 order by c.id";

        return paginationAndCountList(sql, pageSize, pageNo, new Object[]{userid});
    }

    public PageResult findWebProducts(JdataBean parms, int size, int pageno) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes," +
                "CAST(IF ( " +
                "  p.iscontrol = 0 " +
                "  OR pcs.pcs_id IS NOT NULL, " +
                "  TRUE, " +
                "  FALSE " +
                ") AS char) " +
                " AS canbuy, " +
                "CASE WHEN u.price>0 THEN u.price else t.price END price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.unitname,p.code productcode,p.name productname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid "
                + " LEFT JOIN ( " +
                " SELECT " +
                "  wpc.id AS pcs_id, " +
                "  wpc.productid AS pcs_productid " +
                " FROM " +
                "  wb_products_controlsales wpc " +
                " INNER JOIN ( " +
                "  SELECT " +
                "   uu.ID AS u_id, " +
                "   uu.UserGroup AS u_UserGroup, " +
                "   uu.pricegrade AS u_pricegrade, " +
                "   IFNULL(wc.province, '') AS u_province, " +
                "   IFNULL(wc.city, '') AS u_city, " +
                "   IFNULL(wc.region, '') AS u_region " +
                "  FROM " +
                "   wbuser uu " +
                "  LEFT JOIN wb_company wc ON uu.ID = wc.userid " +
                "  WHERE " +
                "   uu.ID = ? " +
                " ) wu " +
                " LEFT JOIN wb_area_trad wat ON ( " +
                "  wpc.type = 3 " +
                "  AND wpc.val = wat.id " +
                " ) " +
                " WHERE " +
                "  (type = 1 AND val = u_UserGroup) " +
                " OR (type = 2 AND val = u_pricegrade) " +
                " OR (type = 4 AND val = u_id) " +
                " OR ( " +
                "  type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(u_province, '') != '' " +
                "    AND wat.province LIKE CONCAT(u_province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(u_province, '') != '' " +
                "    AND wat.province LIKE CONCAT(u_province, '%') " +
                "    AND IFNULL(u_city, '') != '' " +
                "    AND wat.city LIKE CONCAT(u_city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(u_province, '') != '' " +
                "    AND wat.province LIKE CONCAT(u_province, '%') " +
                "    AND IFNULL(u_city, '') != '' " +
                "    AND wat.city LIKE CONCAT(u_city, '%') " +
                "    AND IFNULL(u_region, '') != '' " +
                "    AND wat.area LIKE CONCAT(u_region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  wpc.productid " +
                ") pcs ON p.iscontrol = 1 " +
                " AND pcs.pcs_productid = p.id "
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " left join (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade where u.id=?)u on t.productid=u.productid"
                + " where p.status=2";
        List patmlist = new ArrayList();
        patmlist.add(parms.get("userId"));
        patmlist.add(parms.get("userId"));
        if (parms != null) {
            Object[] keys = parms.getDataMap().keySet().toArray();
            for (Object key : keys) {
                if (StringUtils.isNotBlank(parms.getString(key.toString()))) {
                    if ("classcode".equals(key.toString()) && !"1".equals(parms.getString(key.toString()))) {
                        sql += " and t.productid in (select productid from wb_products_and_class where (classcode like ? or classcode=?))";
                        patmlist.add(parms.getString(key.toString()) + ".%");
                        patmlist.add(parms.getString(key.toString()));
                    } else if ("attritems".equals(key.toString()) && StringUtils.isNotBlank(parms.getString(key.toString()))) {
                        ParmSqlVo vo = ConversionUtil.arrayToInSql(parms.getString(key.toString()).split(","));
                        sql += " and t.productid in (select productid from wb_products_attrval where val in (" + vo.getInSql() + "))";
                        for (Object obj : vo.getParms())
                            patmlist.add(obj);
                    } else if ("instock".equals(key.toString()) && "1".equals(parms.getString(key.toString()))) {
                        sql += " and t.amount>0";
                    } else if ("querylike".equals(key.toString())) {
                        sql += " and (p.name like ? or p.acronymname like ? or t.name like ? or p.code like ? or t.productid in (select b.productid from wb_products_attr a,wb_products_attrval b where a.id=b.attrid and ((a.code='B_tymz' and b.val like ?) or (a.code='B_chanjia' and b.val like ?))))";
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                        patmlist.add("%" + parms.getString(key.toString()) + "%");
                    } else if ("floorid".equals(key.toString())) {
                        //楼层筛选
                        sql += " and t.id in (select priceid from wb_floor_item_products i,wb_floor_item f where i.itemid=f.id and f.floorid=?)";
                        patmlist.add(parms.getString(key.toString()));
                    } else if ("flooritemid".equals(key.toString())) {
                        //楼层类目筛选
                        sql += " and t.id in (select priceid from wb_floor_item_products i where itemid=?)";
                        patmlist.add(parms.getString(key.toString()));
                    }
                }
            }
        }
        String order = "";
        if (parms != null) {
            if ("1".equals(parms.getString("ordertype")))
                order = " t.popularity " + parms.get("orderby", "[asc,desc]");
            else if ("2".equals(parms.getString("ordertype")))
                order = " t.salesnum " + parms.get("orderby", "[asc,desc]");
            else if ("3".equals(parms.getString("ordertype")))
                order = " price " + parms.get("orderby", "[asc,desc]");

        }
        if (StringUtils.isNotBlank(order))
            sql += " order by " + order;
        else
            sql += " order by p.orderno,p.id";
        return querySql2(sql, patmlist, size, pageno);
    }

    public List<JdataBean> findWebProductsBarcodde(String barcode, String userid) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes,t.price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.unitname,p.code productcode,p.name productname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " where p.status=2 and p.barcode = ? ";
        if (StringUtils.isNotEmpty(userid)) {
            sql = "select t.id,t.productid,t.name,t.attrcodes,CASE WHEN u.price>0 THEN u.price else t.price END price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.unitname,p.code productcode,p.name productname,p.attrvals,p.iscontrol,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                    + " from wb_products_prices t"
                    + " inner join wb_products p on p.id=t.productid"
                    + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                    + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                    + " left join (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade where u.id=?) u on t.productid=u.productid"
                    + " where p.status=2 and p.barcode = ? ";
        }
        List patmlist = new ArrayList();
        if (StringUtils.isNotEmpty(userid)) {
            patmlist.add(userid);
        }
        patmlist.add(barcode);
        sql += " order by p.orderno,p.id";
        return querySql2(sql, patmlist);
    }

    public PageResult findHotProducts(String userId, int pageSize, int pageNo) {
        String sql = "select c.productid,c.orderno,c.priceid,c.status,t.name attrname,t.id attrid,CASE WHEN u.price>0 THEN u.price else t.price END price,t.price_s,p.code productcode,p.name productname,p.unitname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_hot_products c"
                + " inner join wb_products_prices t on t.id=c.priceid"
                + " inner join wb_products p on p.id=c.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " left join (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade where u.id=?)u on t.productid=u.productid"
                + " where  p.status=2 and c.status=2 order by c.orderno desc,c.id desc";
        PageResult pr = paginationAndCountList(sql, pageSize, pageNo, new Object[]{userId});
        List<Map> productlist = pr.getList();
        for (Map product : productlist) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode().toLowerCase(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return pr;
    }

    private List<Map> getProducts(List<Map> list) {
        for (Map product : list) {
            String photo = product.get("photoview") == null ? "" : product.get("photoview").toString();
            if (StringUtils.isBlank(photo))
                photo = product.get("product_photoview") == null ? "" : product.get("product_photoview").toString();
            product.put("photo", photo);
            String attrjson = product.get("attrvals") == null ? "" : product.get("attrvals").toString();
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode().toLowerCase(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.get("productid"), ew);
                }
            }
        }
        return list;
    }

    public PageResult findFloorProducts(String userid, String floorid, String itemid, int size, int pageno) {
        String sql = "select t.id,t.productid,t.name,t.attrcodes,CASE WHEN u.price>0 THEN u.price else t.price END price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) amount,p.unitname,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " left join (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade where u.id=?)u on t.productid=u.productid"
                + " where p.status=2";
        List patmlist = new ArrayList();
        patmlist.add(userid);
        if (StringUtils.isNotBlank(floorid)) {
            sql += " and t.id in (select priceid from wb_floor_item_products where itemid in (select id from wb_floor_item where floorid=?))";
            patmlist.add(floorid);
        }
        if (StringUtils.isNotBlank(itemid)) {
            sql += " and t.id in (select priceid from wb_floor_item_products where itemid =?)";
            patmlist.add(itemid);
        }
        sql += " order by p.orderno,p.id";
        PageResult pr = querySql2(sql, patmlist, size, pageno);
        List<JdataBean> productlist = pr.getList();
        for (JdataBean product : productlist) {
            String photo = product.getString("photoview");
            if (StringUtils.isBlank(photo)) photo = product.getString("product_photoview");
            product.put("photo", photo);
            String attrjson = product.getString("attrvals");
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.getString("productid"), ew);
                }
            }
        }
        return pr;
    }

    public BigDecimal findAppositePrice(String userid, String productid) {
        String sql = "SELECT " +
                " IFNULL(pp.price, wpp.price) price " +
                "FROM " +
                " wb_products_prices wpp " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pp.productid, " +
                "  pp.price " +
                " FROM " +
                "  wbuser wu " +
                " JOIN wb_products_pricegrade pp ON pp.grade = wu.pricegrade " +
                " WHERE " +
                "  pp.productid = ? " +
                " AND wu.id = ? " +
                " AND wu.state = 1 " +
                " AND wu.authstate = 2 " +
                ") pp ON wpp.productid = pp.productid " +
                "WHERE " +
                " wpp.productid = ?";
        Object price = uniqueColumnResult(sql, new Object[]{productid, userid, productid});
        return BigDecimal.valueOf(Double.parseDouble(String.valueOf(price)));
    }

    public Map findBasicProductAttrValByProductId(Object productId) {
        String sql = "SELECT GROUP_CONCAT(DISTINCT CASE code WHEN 'B_pzwh' THEN val ELSE NULL END) B_pzwh,GROUP_CONCAT(DISTINCT CASE code WHEN 'B_tymz' THEN val ELSE NULL END) B_tymz,GROUP_CONCAT(DISTINCT CASE code WHEN 'B_guige' THEN val ELSE NULL END) B_guige,GROUP_CONCAT(DISTINCT CASE code WHEN 'B_chanjia' THEN val ELSE NULL END) B_chanjia,GROUP_CONCAT(DISTINCT CASE code WHEN 'B_zhuanl' THEN val ELSE NULL END) B_zhuanl,GROUP_CONCAT(DISTINCT CASE code WHEN 'B_kechai' THEN val ELSE NULL END) B_kechai" +
                " from wb_products_attr m" +
                " join wb_products_attrval n on m.id=n.attrid" +
                " WHERE n.productid=?";
        return uniqueMapResult(sql, new Object[]{productId});
    }

    public boolean isControlsales(String userId, Object productId) {
        String sql = "select EXISTS (SELECT" +
                " 1" +
                " FROM" +
                " wb_products wp" +
                " LEFT JOIN wb_products_controlsales wpc ON wp.id = wpc.productid" +
                "  JOIN (" +
                " SELECT" +
                " wu.pricegrade u_pricegrade," +
                " wu.usergroup u_usergroup," +
                " wc.province," +
                " wc.city," +
                " wc.region" +
                " FROM" +
                " wb_company wc" +
                " JOIN wbuser wu ON wc.userid = wu.id" +
                " WHERE" +
                " wc.state = 2" +
                " AND wu.id = ?" +
                " ) wuc" +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val" +
                "  WHERE" +
                " wp.`status` = 2" +
                " AND (" +
                " wp.iscontrol = 0" +
                " OR (" +
                " wp.iscontrol = 1" +
                " AND (" +
                " (" +
                " wpc.type = 1" +
                " AND wpc.val = u_usergroup" +
                " )" +
                " OR (" +
                " wpc.type = 2" +
                " AND wpc.val = u_pricegrade" +
                " )" +
                " OR (" +
                " wpc.type = 3" +
                " AND (" +
                " IFNULL(wuc.province, '') != ''" +
                " AND wat.province LIKE CONCAT(wuc.province, '%')" +
                " AND IFNULL(wat.city, '') = ''" +
                " AND IFNULL(wat.area, '') = ''" +
                " )" +
                " OR (" +
                " IFNULL(wuc.province, '') != ''" +
                " AND wat.province LIKE CONCAT(wuc.province, '%')" +
                " AND IFNULL(wuc.city, '') != ''" +
                " AND wat.city LIKE CONCAT(wuc.city ,'%')" +
                " AND IFNULL(wat.area, '') = ''" +
                ")" +
                " OR (" +
                " (" +
                " IFNULL(wuc.province, '') != ''" +
                " AND wat.province LIKE CONCAT(wuc.province, '%')" +
                " AND IFNULL(wuc.city, '') != ''" +
                " AND wat.city LIKE CONCAT(wuc.city ,'%')" +
                " AND IFNULL(wuc.region, '') != ''" +
                " AND wat.area LIKE CONCAT(wuc.region ,'%')" +
                ")" +
                ")" +
                ")" +
                ")" +
                ")" +
                ")" +
                " AND wp.id = ?)";
        Object isExist = uniqueColumnResult(sql, new Object[]{userId, productId});
        return Integer.parseInt(String.valueOf(isExist)) == 0;
    }


    public int deleteFavorites(String userId, String[] ids) {
        Map map = new HashMap();
        map.put("userid", userId);
        map.put("dataid", ids);
        return delete("wb_favorite", map);
    }

    public List<JdataBean> findProducts(Map map) {
        String sql = "select t.id,t.productid,p.name,p.unitname unit,t.attrcodes,ifnull(u.price,t.price)price,t.price_s,t.salesnum,t.popularity,(ifnull(t.amount,0)-ifnull(t.djamount,0)) inventory,p.unitname,p.code productcode,p.name productname,p.attrvals,p.iscontrol,ifnull(s.viewpath,sm.viewpath) photo"
                + " from wb_products_prices t"
                + " inner join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"//销售属性主图片
                + " left join app_pictures sm on sm.id=p.photo"//商品主图片
                + " left join (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade where u.id=:userid)u on t.productid=u.productid"
                + " where p.status=2 and p.id in (:ids) order by t.id desc";
        return list(sql, map);
    }

    public int getAvailableInventory(String productid) {
        String sql = "SELECT IFNULL(amount,0)-IFNULL(djamount,0) amount from wb_products_prices WHERE productid=?";
        Object amount = uniqueColumnResult(sql, productid);
        return Integer.parseInt(String.valueOf(amount));
    }

    public String findClassCode(String priceid) {
        String sql = "SELECT code from wb_products_class WHERE id=(SELECT classid from wb_products_and_class WHERE productid=?)";
        return String.valueOf(uniqueColumnResult(sql, priceid));
    }

    public List<JdataBean> getDataidByUserid(String userid) {
        String sql = "select dataid from wb_browse where userid = ?";
        return querySql(sql, new Object[]{userid});
    }

    public UrgentProduct findCartProduct(String userid, String pid) {
        String sql = "SELECT prod.name," +
                " IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) amount, " +
                " IFNULL(pp.price, wpp.price) price, " +
                " shop.num, " +
                "if(fav.id is not null,true,false)favorite, " +
                "IF ( " +
                " prod.iscontrol = 0 " +
                " OR wpc.id IS NOT NULL, " +
                " FALSE, " +
                " TRUE " +
                ") control " +
                "FROM " +
                " wb_products prod " +
                "JOIN wb_products_prices wpp ON prod.id = wpp.productid " +
                "LEFT JOIN(SELECT fav.id,fav.dataid from wb_favorite fav WHERE fav.userid=? and fav.dataid =?)fav on prod.id=fav.dataid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pp.productid, " +
                "  pp.price " +
                " FROM " +
                "  wbuser wu " +
                " JOIN wb_products_pricegrade pp ON wu.pricegrade = pp.grade " +
                " WHERE " +
                "  pp.productid = ? " +
                " AND wu.id = ? " +
                " AND wu.state = 1 " +
                " AND wu.authstate = 2 " +
                ") pp ON prod.id = pp.productid " +
                "LEFT JOIN wb_shopcart shop ON ( " +
                " prod.id = shop.productid " +
                " AND shop.userid = ? " +
                ") " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.id, " +
                "  wpc.productid " +
                " FROM " +
                "  wb_products_controlsales wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   comp.province, " +
                "   comp.city, " +
                "   comp.region " +
                "  FROM " +
                "   wbuser wu " +
                "  JOIN wb_company comp ON wu.id = comp.userid " +
                "  WHERE " +
                "   wu.id = ? " +
                "  AND wu.state = 1 " +
                "  AND wu.authstate = 2 " +
                "  AND comp.state = 2 " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wpc.val = wat.id " +
                " WHERE " +
                "  wpc.productid = ? " +
                " AND ( " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 2 " +
                "   AND wpc.val = u_pricegrade " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 3 " +
                "   AND ( " +
                "    ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wat.city, '') = '' " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wuc.region, '') != '' " +
                "     AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "    ) " +
                "   ) " +
                "  ) " +
                " ) " +
                " LIMIT 1 " +
                ") wpc ON prod.id = wpc.productid " +
                "WHERE " +
                " prod.`status` = 2 " +
                "AND prod.id = ?";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{userid, pid, pid, userid, userid, userid, pid, pid}, UrgentProduct.class);
        query.addScalar("control", StandardBasicTypes.BOOLEAN);
        query.addScalar("num", StandardBasicTypes.INTEGER);
        query.addScalar("amount", StandardBasicTypes.INTEGER);
        query.addScalar("price", StandardBasicTypes.BIG_DECIMAL);
        query.addScalar("name");
        query.addScalar("favorite", StandardBasicTypes.BOOLEAN);
        return (UrgentProduct) query.uniqueResult();
    }

    public Product findPowerProduct(String userid, String productid) {
        String sql = "SELECT " +
                " IFNULL(pp.price, wpp.price) price, " +
                " " +
                "IF (fav.id IS NOT NULL, TRUE, FALSE) favorite, " +
                " " +
                "IF ( " +
                " prod.iscontrol = 0 " +
                " OR wpc.productid IS NOT NULL, " +
                " FALSE, " +
                " TRUE " +
                ") control " +
                "FROM " +
                " wb_products prod " +
                "JOIN wb_products_prices wpp ON prod.id = wpp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pp.productid, " +
                "  pp.price " +
                " FROM " +
                "  wbuser wu " +
                " JOIN wb_products_pricegrade pp ON wu.pricegrade = pp.grade " +
                " WHERE " +
                "  pp.productid = ? " +
                " AND wu.id = ? " +
                ") pp ON prod.id = pp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  fav.id, " +
                "  fav.dataid " +
                " FROM " +
                "  wb_favorite fav " +
                " WHERE " +
                "  fav.userid = ? " +
                " AND fav.dataid = ? " +
                ") fav ON prod.id = fav.dataid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.productid " +
                " FROM " +
                "  wb_products_controlsales wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.id, " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   comp.province, " +
                "   comp.city, " +
                "   comp.region " +
                "  FROM " +
                "   wbuser wu " +
                "  JOIN wb_company comp ON wu.id = comp.userid " +
                "  WHERE " +
                "   wu.id = ? " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wpc.val = wat.id " +
                " WHERE " +
                "  wpc.productid = ? " +
                " AND ( " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 2 " +
                "   AND wpc.val = u_pricegrade " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 3 " +
                "   AND ( " +
                "    ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wat.city, '') = '' " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wuc.region, '') != '' " +
                "     AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "    ) " +
                "   ) " +
                "  ) " +
                " ) " +
                " LIMIT 1 " +
                ") wpc ON prod.id = wpc.productid " +
                "WHERE " +
                " prod.`status` = 2 " +
                "AND prod.id = ?";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{productid, userid, userid, productid, userid, productid, productid}, Product.class);
        query.addScalar("price", StandardBasicTypes.BIG_DECIMAL);
        query.addScalar("favorite", StandardBasicTypes.BOOLEAN);
        query.addScalar("control", StandardBasicTypes.BOOLEAN);
        return (Product) query.uniqueResult();
    }

    public Product findOne(String priceid) {
        String sql = "SELECT " +
                " prod.id, " +
                " prod.`name`, " +
                " prod.details, " +
                " GROUP_CONCAT( " +
                "  DISTINCT CASE wpa.attrid " +
                "  WHEN 8 THEN " +
                "   wpa.val " +
                "  ELSE " +
                "   NULL " +
                "  END " +
                " ) specification, " +
                " GROUP_CONCAT( " +
                "  DISTINCT CASE wpa.attrid " +
                "  WHEN 11 THEN " +
                "   wpa.val " +
                "  ELSE " +
                "   NULL " +
                "  END " +
                " ) manufacturers, " +
                " wfi.Item_name unit " +
                "FROM " +
                " wb_products prod " +
                "JOIN wb_products_attrval wpa ON prod.id = wpa.productid " +
                "LEFT JOIN Wfwordbook_item wfi ON ( " +
                " prod.unit = wfi.Item_val " +
                " AND wfi.`code` = 'ProductUnit' " +
                ") " +
                "WHERE " +
                " prod.id = ? " +
                "AND prod.`status` = 2";
        SQLQuery query = createBeanResultSQLQuery(sql, priceid, Product.class);
        return (Product) query.uniqueResult();
    }

    public Product findPowerProduct(String priceid) {
        String sql = "SELECT IFNULL(wpp.amount,0)-IFNULL(wpp.djamount,0)amount from wb_products prod"
                + " join wb_products_prices wpp on prod.id=wpp.productid"
                + " WHERE prod.id=? and prod.`status`=2 limit 1";
        SQLQuery query = createBeanResultSQLQuery(sql, priceid, Product.class);
        query.addScalar("amount", StandardBasicTypes.INTEGER);
        return (Product) query.uniqueResult();
    }

    public List<Picture> findPhotos(String priceid) {
        String sql = "SELECT " +
                " filepath " +
                "FROM " +
                " wb_products_prices prod " +
                "LEFT JOIN app_pictures pic ON prod.photodataid = pic.dataid " +
                "WHERE " +
                " prod.id = ? " +
                "AND pic.deltag = 0 " +
                "ORDER BY " +
                " pic.orderno, " +
                " pic.id";
        SQLQuery query = createBeanResultSQLQuery(sql, priceid, Picture.class);
        return getObject(query.list());
    }

    public List<Product> findPowerProducts(String userid, String ids) {
        String sql = "SELECT " +
                " prod. id, " +
                " IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) amount, " +
                " IFNULL(pp.price, wpp.price) price, " +
                "IF (fav.id IS NOT NULL, TRUE, FALSE) favorite " +
                "FROM " +
                " wb_products prod " +
                "JOIN wb_products_prices wpp ON prod.id = wpp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  fav.id, " +
                "  fav.dataid " +
                " FROM " +
                "  wb_favorite fav " +
                " WHERE " +
                "  fav.userid =? " +
                " AND fav.dataid in(" + ids + ") " +
                ") fav ON prod.id = fav.dataid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pp.productid, " +
                "  pp.price " +
                " FROM " +
                "  wbuser wu " +
                " JOIN wb_products_pricegrade pp ON wu.pricegrade = pp.grade " +
                " WHERE " +
                "  pp.productid in( " + ids + ") " +
                " AND wu.id = ? " +
                " AND wu.state = 1 " +
                " AND wu.authstate = 2 " +
                ") pp ON prod.id = pp.productid " +
                "WHERE " +
                " prod.`status` = 2 " +
                "AND prod.id in( " + ids + ")";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{userid, userid}, Product.class);
        query.addScalar("id");
        query.addScalar("amount", StandardBasicTypes.INTEGER);
        query.addScalar("price");
        query.addScalar("favorite", StandardBasicTypes.BOOLEAN);
        return getObject(query.list());
    }

    public List<JdataBean> findPowerProducts(Map<String, Object> parms) {
        String sql = "SELECT " +
                " prod.id, " +
                " prod.`name`, " +
                " prod.`status`, " +
                " IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) amount, " +
                " IFNULL(pp.price, wpp.price) price, " +
                " " +
                "IF ( " +
                " prod.iscontrol = 0 " +
                " OR wpc.productid IS NOT NULL, " +
                " FALSE, " +
                " TRUE " +
                ") control " +
                "FROM " +
                " wb_products prod " +
                "JOIN wb_products_prices wpp ON prod.id = wpp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pp.productid, " +
                "  pp.price " +
                " FROM " +
                "  wbuser wu " +
                " JOIN wb_products_pricegrade pp ON wu.pricegrade = pp.grade " +
                " WHERE " +
                "  pp.productid IN (:ids) " +
                " AND wu.id = :userid " +
                ") pp ON prod.id = pp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.productid " +
                " FROM " +
                "  wb_products_controlsales wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   comp.province, " +
                "   comp.city, " +
                "   comp.region " +
                "  FROM " +
                "   wbuser wu " +
                "  JOIN wb_company comp ON wu.id = comp.userid " +
                "  WHERE " +
                "   wu.id = :userid " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wpc.val = wat.id " +
                " WHERE " +
                "  wpc.productid IN (:ids) " +
                " AND ( " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 2 " +
                "   AND wpc.val = u_pricegrade " +
                "  ) " +
                "  OR ( " +
                "   wpc.type = 3 " +
                "   AND ( " +
                "    ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wat.city, '') = '' " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wat.area, '') = '' " +
                "    ) " +
                "    OR ( " +
                "     IFNULL(wuc.province, '') != '' " +
                "     AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "     AND IFNULL(wuc.city, '') != '' " +
                "     AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "     AND IFNULL(wuc.region, '') != '' " +
                "     AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "    ) " +
                "   ) " +
                "  ) " +
                " ) " +
                ") wpc ON prod.id = wpc.productid " +
                "WHERE " +
                " prod.id IN (:ids)";
        return list(sql, parms);
    }

    public List<JdataBean> findProductsInfo(Map<String, Object> parms) {
        String sql = "SELECT " +
                " prod.id productid,prod.name, " +
                " IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) amount, " +
                " IFNULL(pp.price, wpp.price) price, " +
                " pfs.promotion_type fs_type, " +
                " pfs.id fs_promotionid, " +
                " pfs.amount fs_amount, " +
                " pfs.price fs_price, " +
                " pfs.time fs_time, " +
                " pbs.promotion_type bs_type, " +
                " pbs.id bs_promotionid, " +
                " pbs.buy_number, " +
                " pbs.send_number, " +
                " pbs.time bs_time " +
                "FROM " +
                " wb_products prod " +
                "JOIN wb_products_prices wpp ON prod.id = wpp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pp.price, " +
                "  pp.productid " +
                " FROM " +
                "  wbuser wu " +
                " JOIN wb_products_pricegrade pp ON wu.pricegrade = pp.grade " +
                " WHERE " +
                "  wu.id = :userid " +
                " AND pp.productid IN (:ids) " +
                ") pp ON prod.id = pp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  prom.id, " +
                "  prom.promotion_type, " +
                "  prom.product_val, " +
                " " +
                " IF ( " +
                "  prom.time IS NOT NULL, " +
                "  prom.time - IFNULL(wph.time, 0), " +
                "  NULL " +
                " ) time, " +
                " pfs.amount, " +
                " pfs.price " +
                "FROM " +
                " wb_promotions prom " +
                "JOIN wb_promotion_flash_sale pfs ON prom.id = pfs.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  SUM(IFNULL(wph.times, 0)) time, " +
                "  wph.promotionid " +
                " FROM " +
                "  wb_promotion_histories wph " +
                " WHERE " +
                "  wph.userid = :userid " +
                " GROUP BY " +
                "  wph.promotionid " +
                ") wph ON prom.id = wph.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wu.id = :userid " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                ") pct ON prom.id = pct.promotion_id " +
                "WHERE " +
                " pfs.amount > 0 " +
                "AND prom.promotion_type = 0 " +
                "AND ( " +
                " prom.platform = 0 " +
                " OR prom.platform = :platform " +
                ") " +
                "AND ( " +
                " NOW() BETWEEN prom.start_time " +
                " AND prom.end_time " +
                ") " +
                "AND prom.state = 1 " +
                "AND prom.product_type = 1 " +
                "AND prom.product_val IN (:ids) " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND prom.id = pct.promotion_id " +
                " ) " +
                ") " +
                ") pfs ON prod.id = pfs.product_val " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  prom.id, " +
                "  prom.promotion_type, " +
                "  prom.product_val, " +
                " " +
                " IF ( " +
                "  prom.time IS NOT NULL, " +
                "  prom.time - IFNULL(wph.time, 0), " +
                "  NULL " +
                " ) time, " +
                " pbs.buy_number, " +
                " pbs.send_number " +
                "FROM " +
                " wb_promotions prom " +
                "JOIN wb_promotion_buy_send pbs ON prom.id = pbs.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  SUM(IFNULL(wph.times, 0)) time, " +
                "  wph.promotionid " +
                " FROM " +
                "  wb_promotion_histories wph " +
                " WHERE " +
                "  wph.userid = :userid " +
                " GROUP BY " +
                "  wph.promotionid " +
                ") wph ON prom.id = wph.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wu.id = :userid " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                ") pct ON prom.id = pct.promotion_id " +
                "WHERE " +
                " prom.promotion_type = 1 " +
                "AND ( " +
                " prom.platform = 0 " +
                " OR prom.platform = :platform " +
                ") " +
                "AND ( " +
                " NOW() BETWEEN prom.start_time " +
                " AND prom.end_time " +
                ") " +
                "AND prom.state = 1 " +
                "AND prom.product_type = 1 " +
                "AND prom.product_val IN (:ids) " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND prom.id = pct.promotion_id " +
                " ) " +
                ") " +
                "AND ( " +
                " prom.time IS NULL " +
                " OR prom.time > ifnull(wph.time, 0) " +
                ") " +
                ") pbs ON prod.id = pbs.product_val " +
                "WHERE " +
                " prod.id IN (:ids)";
        return list(sql, parms);
    }

    public List<JdataBean> queryPricegradeAndControlsales(String userid, List<String> productids) {
        if(productids == null || productids.size() == 0) {
            return new ArrayList<JdataBean>();
        }
        List<Object> parms = new ArrayList<>();
        parms.add(userid);
        parms.add(userid);
        parms.addAll(productids);
        StringBuffer inStr  = new StringBuffer();
        for (int i = 0 ; i < productids.size() ; i++) {
            inStr.append(",?");
        }
        String instr = inStr.substring(1);
        String sql = "SELECT " +
                "wp.id, " +
                "IF ( " +
                "wp.iscontrol = 0 " +
                "OR pcs.pcs_productid IS NOT NULL, " +
                "TRUE, " +
                "FALSE " +
                ") AS canbuy, " +
                " u.price " +
                "FROM " +
                "wb_products wp " +
                "LEFT JOIN ( " +
                "SELECT " +
                "wpc.id AS pcs_id, " +
                "wpc.productid AS pcs_productid " +
                "FROM " +
                "wb_products_controlsales wpc " +
                "INNER JOIN ( " +
                "SELECT " +
                "uu.ID AS u_id, " +
                "uu.UserGroup AS u_UserGroup, " +
                "uu.pricegrade AS u_pricegrade, " +
                "IFNULL(wc.province, '') AS u_province, " +
                "IFNULL(wc.city, '') AS u_city, " +
                "IFNULL(wc.region, '') AS u_region " +
                "FROM " +
                "wbuser uu " +
                "LEFT JOIN wb_company wc ON uu.ID = wc.userid " +
                "WHERE " +
                "uu.ID = ? " +
                ") wu " +
                "LEFT JOIN wb_area_trad wat ON ( " +
                "wpc.type = 3 " +
                "AND wpc.val = wat.id " +
                ") " +
                "WHERE " +
                "(type = 1 AND val = u_UserGroup) " +
                "OR (type = 2 AND val = u_pricegrade) " +
                "OR ( " +
                "type = 3 " +
                "AND ( " +
                "( " +
                "IFNULL(u_province, '') != '' " +
                "AND wat.province LIKE CONCAT(u_province, '%') " +
                "AND IFNULL(wat.city, '') = '' " +
                "AND IFNULL(wat.area, '') = '' " +
                ") " +
                "OR ( " +
                "IFNULL(u_province, '') != '' " +
                "AND wat.province LIKE CONCAT(u_province, '%') " +
                "AND IFNULL(u_city, '') != '' " +
                "AND wat.city LIKE CONCAT(u_city, '%') " +
                "AND IFNULL(wat.area, '') = '' " +
                ") " +
                "OR ( " +
                "IFNULL(u_province, '') != '' " +
                "AND wat.province LIKE CONCAT(u_province, '%') " +
                "AND IFNULL(u_city, '') != '' " +
                "AND wat.city LIKE CONCAT(u_city, '%') " +
                "AND IFNULL(u_region, '') != '' " +
                "AND wat.area LIKE CONCAT(u_region, '%') " +
                ") " +
                ") " +
                ") " +
                "OR (type = 4 AND wpc.val = u_id) " +
                "GROUP BY " +
                "wpc.productid " +
                ") pcs ON wp.id = pcs.pcs_productid " +
                "LEFT JOIN ( " +
                "SELECT " +
                "pp.productid, " +
                "pp.price " +
                "FROM " +
                "wbuser u " +
                "JOIN wb_products_pricegrade pp ON u.pricegrade = pp.grade " +
                "WHERE " +
                "u.id = ? " +
                ") u ON wp.id = u.productid " +
                "WHERE " +
                "wp.id IN (" + instr +
                ")";
        return querySql2(sql,parms);
    }
}
